import db from '@/common/sqlite.js'

async function openDB() {
	if(!db.isOpen()){
		await db.open()
	}
}
async function closeDB() {
	if(db.isOpen()) {
		await db.close()
	}
}
/*
	@Description: 年度收支统计
	year string
*/
export async function getAllAccounts(year) {
	await openDB()
	const monArr = [],list = []
	for (let i = 1; i <= 12; i++) {
	  monArr.push(i.toString().padStart(2,'0'))
	}
		const paySql = `SELECT ROUND(sum(num),2) AS 'mon_pay',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 0 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
		const inSql = `SELECT ROUND(sum(num),2) AS 'mon_in',STRFTIME('%m', tb_record.create_time) AS 'mon' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 1 AND STRFTIME('%Y', tb_record.create_time) = '${year}' GROUP BY mon`;
		const payList = await db.selectTableDataBySql(paySql);
		const inList = await db.selectTableDataBySql(inSql);
		// console.log(payList,inList)
		for (let mon of monArr) {
			list.push({
				mon:mon,
				pay:(payList.filter(v=>v.mon==mon))[0]?.mon_pay || 0,
				in:(inList.filter(v=>v.mon==mon))[0]?.mon_in || 0
			})
		}
		return list
		// console.log(list)
	await closeDB()
}

/*
	@Description: 总收支统计
*/
export async function getTotalAccounts() {
	await openDB()
	const list = []
		const paySql = `SELECT ROUND(sum(num),2) AS 'year_pay',STRFTIME('%Y', tb_record.create_time) AS 'year' from tb_type , tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 0 GROUP BY year ORDER BY year desc`;
		const inSql = `SELECT ROUND(sum(num),2) AS 'year_in',STRFTIME('%Y', tb_record.create_time) AS 'year' from tb_type,tb_record WHERE tb_type.id = tb_record.type_id AND tb_type.type = 1 GROUP BY year ORDER BY year desc`;
		const payList = await db.selectTableDataBySql(paySql);
		const inList = await db.selectTableDataBySql(inSql);
		const yearArr = (await getAllYear()).reverse()
		// console.log(payList,inList)
		for (let year of yearArr) {
			list.push({
				year,
				pay:(payList.filter(v=>v.year==year))[0]?.year_pay || 0,
				in:(inList.filter(v=>v.year==year))[0]?.year_in || 0
			})
		}
		return list
		// console.log(list)
	await closeDB()
}

/*
	@Description: 添加收支记录
	@ obj Object
*/
export async function addRecord(data,showToast=true) {
	await openDB()
	const col = `'type_id','num','create_time','remark','image'`
	const dataSql = `(${data.type_id},${data.num},'${data.create_time}','${data.remark}','${data.image??''}')`
	try{
		await db.insertTableData('tb_record',dataSql,col)
		showToast && uni.showToast({
			title:'记录成功'
		})
	}catch(e){
		//TODO handle the exception
		uni.showToast({
			title:'记录失败',
			icon:'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
/*
	@Description: 编辑记录
	@ obj Object
*/
export async function updateRecord(data) {
	await openDB()
	const dataSql = `type_id = ${data.type_id},num = ${data.num},create_time = '${data.create_time}',remark = '${data.remark}',image='${data.image}'`
	try{
		await db.updateTableData('tb_record',dataSql,'id',data.id)
		uni.showToast({
			title:'编辑成功'
		})
	}catch(e){
		//TODO handle the exception
		uni.showToast({
			title:'编辑失败',
			icon:'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
/*
	@Description: 删除记录
	@ id number
*/
export async function delRecord(id) {
	// console.log(id)
	await openDB()
	try{
		await db.deleteTableData('tb_record','id',id)
		uni.showToast({
			title:"删除成功"
		})
	}catch(e){
		// console.log(JSON.stringify(e))
		uni.showToast({
			title:"删除失败",
			icon:'fail'
		})
		//TODO handle the exception
	}finally {
		await closeDB()
	}
}
// 获取首页数据
export async function getHome(currentMonth) {
	const homeData = {
		curMonPay:0,//本月支出
		curMonIn:0,//本月收入
		surplus:0,//本月结余
		list:[]//收支明细
	}
	await openDB();
	// const date = new Date()
	// const year = date.getFullYear()
	// const mon = (date.getMonth()+1).toString().padStart(2,'0')
	// const currentMonth = year+'-'+mon
	// console.log(await db.selectTableData('tb_record'))
		// const sql = `ALTER TABLE tb_type MODIFY COLUMN 'is_custom' integer DEFAULT ''`
		// await db.ExecuteSQL(sql)
	const currentMonthSql = `SELECT DISTINCT STRFTIME('%m-%d', tb_record.create_time) AS 'date' FROM tb_record  WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' ORDER BY tb_record.create_time DESC`
	const curMonDayAccounts = `SELECT tb_record.id,tb_record.type_id,tb_type.icon,tb_type.title,tb_record.num , tb_type.type,STRFTIME('%m-%d', tb_record.create_time) AS 'date', STRFTIME('%H:%M:%S', tb_record.create_time) AS 'time',tb_record.remark,tb_record.image  FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' and tb_type.id = tb_record.type_id ORDER BY date DESC, time DESC`
	try{
		const dateList = await db.selectTableDataBySql(currentMonthSql);
		// console.log(dateList)
		if (dateList.length==0) {
			return homeData
		}
		const accounts = await db.selectTableDataBySql(curMonDayAccounts);
		// console.log(JSON.stringify(accounts))
		for (let date of dateList) {
			const obj = {
				date:'',
				list:'',
			}
			obj.date = date.date
			const arr = [],dateIn=0,datePay=0;
			for (let acc of accounts) {
				if(date.date == acc.date) {
					arr.push(acc)
				}
			}
			obj.dateIn = dateIn
			obj.datePay = datePay
			obj.list = arr
			homeData.list.push(obj)
		}
		// console.log(homeData)
		const paySql = `SELECT ROUND(sum(num),2) as sum FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' AND tb_type.id = tb_record.type_id AND tb_type.type=0`
		const payNum = await db.selectTableDataBySql(paySql)
		const inSql = `SELECT ROUND(sum(num),2) as sum FROM tb_record,tb_type WHERE STRFTIME('%Y-%m', tb_record.create_time)='${currentMonth}' AND tb_type.id = tb_record.type_id AND tb_type.type=1`
		const inNum = await db.selectTableDataBySql(inSql)
		// console.log(JSON.stringify(inNum))
		homeData.curMonPay = payNum[0].sum || 0
		homeData.curMonIn = inNum[0].sum || 0
		// console.log(JSON.stringify(homeData))
		return homeData
	}catch(e){
		return homeData
		//TODO handle the exception
	}
	await closeDB()
}

/*
	@Description:图表页,图表
	@type: number 类型 1收入 0支出
	@dateType: 日期类型 0周,1月,2年
	@dateValue: string 今年第几周 /月 01，02.||年 2024
*/
export async function getLine(type,dateType,dateValue) {
	// console.log(dateValue)
	await openDB()
	try{
		let dateCondition = '',groupCondition=''
		switch(dateType) {
			case 0:{
				dateCondition = `AND STRFTIME('%Y-%W', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'week'
			}break;
			case 1:{
				dateCondition = `AND STRFTIME('%Y-%m', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'date'
			}break;
			case 2:{
				dateCondition = `AND STRFTIME('%Y', tb_record.create_time) = '${dateValue}'`
				groupCondition = 'mon'
			}break;
		}
		const sql = `SELECT ROUND(sum(num),2) AS num, STRFTIME('%m', tb_record.create_time) as mon, STRFTIME('%m-%d', tb_record.create_time) as date, STRFTIME('%w', tb_record.create_time) as week FROM tb_record INNER JOIN tb_type ON tb_type.id = tb_record.type_id AND tb_type.type=${type} ${dateCondition} GROUP BY ${groupCondition} ORDER BY tb_record.create_time`
		// console.log(sql)
		const list = await db.selectTableDataBySql(sql)
		// console.log(list)
		return list
	}catch(e){
		//TODO handle the exception
		return []
	}finally {
		await closeDB()
	}
}


/*
	@Description: 图表页，获取收支排行榜
	@type Number: 类型 1收入，0支出
	@dateType: 日期类型 0周,1月,2年
	@dateValue: string 今年第几周 /月 01，02.||年 2024
*/
export async function getRank(type,dateType,dateValue) {
	await openDB()
	try{
		let dateCondition = ''
		switch(dateType) {
			case 0:{
				dateCondition = `AND STRFTIME('%Y-%W', tb_record.create_time) = '${dateValue}'`
			}break;
			case 1:{
				dateCondition = `AND STRFTIME('%Y-%m', tb_record.create_time) = '${dateValue}'`
			}break;
			case 2:{
				dateCondition = `AND STRFTIME('%Y', tb_record.create_time) = '${dateValue}'`
			}break;
		}
		const sql = `SELECT ROUND(sum(num),2) AS 'num' ,COUNT(num) AS count,tb_type.icon, tb_type.title,tb_type.id AS type_id FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.type = ${type} ${dateCondition} GROUP BY tb_type.title ORDER BY num DESC`
		const list = db.selectTableDataBySql(sql);
		return list
	}catch(e){
		//TODO handle the exception
		return []
	}finally{
		await closeDB()
	}
}
// 获取记录中的年份列表
export async function getAllYear() {
	await openDB()
	try{
		const sql = `SELECT DISTINCT STRFTIME('%Y', tb_record.create_time) AS year FROM tb_record ORDER BY STRFTIME('%Y', tb_record.create_time)`
		const yearList = await db.selectTableDataBySql(sql)
		return yearList.map(obj => obj.year)
	}catch(e){
		//TODO handle the exception
		return []
	}finally {
		await closeDB()
	}
}

/*
	@Description: 图表页详情，获取收支排行榜item详细
	@type_id Number: 收支分类
	@dateType: 日期类型 0周,1月,2年
	@dateValue: string 今年第几周 /月 01，02.||年 2024
*/
export async function getRankDetail(type_id,dateType,dateValue) {
	await openDB()
	try{
		let dateCondition = '';
		switch(parseInt(dateType)) {
			case 0:{
				dateCondition = `AND STRFTIME('%Y-%W', tb_record.create_time) = '${dateValue}'`
			}break;
			case 1:{
				dateCondition = `AND STRFTIME('%Y-%m', tb_record.create_time) = '${dateValue}'`
			}break;
			case 2:{
				dateCondition = `AND STRFTIME('%Y', tb_record.create_time) = '${dateValue}'`
			}break;
		}
		const sql = `SELECT num ,remark,image,tb_type.icon, tb_type.title,tb_type.id,STRFTIME('%m-%d', tb_record.create_time) AS mon,STRFTIME('%H:%M:%S', tb_record.create_time) AS time FROM tb_type INNER JOIN tb_record ON tb_type.id = tb_record.type_id AND tb_type.id = ${type_id} ${dateCondition} ORDER BY tb_record.create_time DESC`
		// console.log(sql)
		const list = db.selectTableDataBySql(sql);
		return list
	}catch(e){
		//TODO handle the exception
		return []
	}finally{
		await closeDB()
	}
}
// 获取type 列表
export async function getTypeList() {
	await openDB()
	try{
		const list = await db.selectTableData('tb_type')
		return {code:1,list}
	}catch(e){
		
	}finally{
		await closeDB()
	}
}
export async function getRecordList() {
	await openDB()
	try{
		const list = await db.selectTableData('tb_record')
		return {code:1,list}
	}catch(e){
		
	}finally{
		await closeDB()
	}
}
/*
	@Description: 批量导入数据，
		1.导入自定义类型，
			如果存在，不导入，修改recordList type外键
			如果不存在，导入，修改recordList type外键
		2.导入record
			如果存在，不导入
			如果不存在，导入
*/
export async function batchImport(data) {
	// console.log('shuju',data)
	if(JSON.stringify(data)=='{}') {
		uni.showToast({
			title:'无数据',
			icon:'error'
		})
		return
	}
	// 期望导入的数据
	const {customTypeList,recordList} = data
	// console.log(customTypeList,recordList)
	if(customTypeList.length==0&&recordList.length==0) {
		uni.showToast({
			title:'无数据',
			icon:'error'
		})
		return 
	}
	uni.showLoading({
		title:'正在导入...'
	})
	// 获取已存在自定义类型列表
	let typeArr = await getCustomTypeList()
	// 获取已存在记录列表
	const recordArr = (await getRecordList()).list
	// 获取应该导入的自定义类型列表,即不存在的自定义类型
	let shouldInType = customTypeList.filter(expectItem => {
		for (let item of typeArr) {
			if(item.title === expectItem.title) {
				return false
			}
		}
		return true
	})
	// 导入自定义类型
	for (let item of shouldInType) {
		await addCustomType(item,false)
	}
	// 重新赋值id，修改recordListId值
	typeArr = await getCustomTypeList()
	shouldInType = shouldInType.map(item => {
		for (let nItem of typeArr) {
			if(item.title === nItem.title && item.type===nItem.type) {
				item.newId = nItem.id
				break
			}
		}
		return item
	})
	// console.log(shouldInType)
	// 获取应该导入的记录列表，即不存在的记录
	let shouldInRecord = recordList.filter(expectItem => {
		for (let item of recordArr) {
			if(item.create_time===expectItem.create_time&&item.num===expectItem.num) {
				return false
			}
		}
		return true
	})
	// 修改自定义type_id
	shouldInRecord = shouldInRecord.map(item => {
		for (let itemType of shouldInType) {
			if(item.type_id==itemType.id) {
				item.type_id = itemType.newId
				break
			}
		}
		return item
	})
	// 添加record记录
	for (let item of shouldInRecord) {
		await addRecord(item,false)
	}
	uni.hideLoading()
	uni.showToast({
		title:'导入成功'
	})
}

// 添加自定义收支类型
/*
	@obj object {title:'',type:0||1}
*/
export async function addCustomType(obj,showToast=true) {
	await openDB()
	// const col = await db.selectTableDataBySql(`PRAGMA table_info(tb_type)`)
	// const hasCustomCol = (col.filter(item => item.name=='is_custom')).length>0
	// console.log(hasCustomCol)
	// if(!hasCustomCol) {
	// 	// 添加字段 is_custom
	// 	const sql = `ALTER TABLE tb_type ADD COLUMN 'is_custom' integer DEFAULT 0`
	// 	await db.ExecuteSQL(sql)
	// }
	try{
		// 添加自定义类型
		const col = "'title','icon','type','is_custom'"
		const dataStr = `('${obj.title}','icon-owner_set',${obj.type},1)`;
		await db.insertTableData('tb_type',dataStr,col)
		showToast && uni.showToast({
			title:"添加成功"
		})
	}catch(e){
		//TODO handle the exception
		uni.showToast({
			title:"添加失败",
			icon:'fail'
		})
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
/*
	修改自定义类型
*/
export async function updateCustomType(obj) {
	await openDB()
	try{
		await db.updateTableData('tb_type',`title='${obj.title}'`,'id',obj.id)
		uni.showToast({
			title:"修改成功"
		})
	}catch(e){
		uni.showToast({
			title:"修改失败",
			icon:'fail'
		})
		//TODO handle the exception
	}
	await closeDB()
}

export async function deleteCustomType(id) {
	await openDB()
	try{
		// 删除records记录
		await db.deleteTableData('tb_record','type_id',id)
		// 删除type
		await db.deleteTableData('tb_type','id',id)
		uni.showToast({
			title:"删除成功"
		})
		
	}catch(e){
		uni.showToast({
			title:"删除失败",
			icon:'fail'
		})
		//TODO handle the exception
	}
	await closeDB()
}
export async function getCustomTypeList() {
	await openDB()
	try{
		return await db.selectTableData('tb_type','is_custom',1)
	}catch(e){
		//TODO handle the exception
		console.log(JSON.stringify(e))
	}
	await closeDB()
}
// 创建收支类型表,并插入初始数据
async function createType() {
	// 删除原数据表
	try{
		await db.dropTable('tb_type')
	}catch(e){
		//TODO handle the exception
		console.log(e)
		return false
	}
	// 创建类型表
	const data = '"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"title" TEXT,"icon" TEXT,"type" integer, "is_custom" integer DEFAULT 0';
	try{
		await db.createTable('tb_type',data)
	}catch(e){
		//TODO handle the exception
		console.log(e)
		return false
	}
	// 字段
	const col = "'title','icon','type'"
	// 数据
	const payType = [
		{
		title:'餐饮',
		icon:'icon-canyin',
	},{
		title:'购物',
		icon:'icon-cart',
	},{
		title:'日用',
		icon:'icon-shijin',
	},{
		title:'交通',
		icon:'icon-jiaotong',
	},{
		title:'蔬菜',
		icon:'icon-huluobu',
	},{
		title:'水果',
		icon:'icon-shuiguo',
	},{
		title:'零食',
		icon:'icon-lingshi',
	},{
		title:'运动',
		icon:'icon-sport',
	},{
		title:'娱乐',
		icon:'icon-yule',
	},{
		title:'服饰',
		icon:'icon-Txu',
	},{
		title:'美容',
		icon:'icon-huazhuangjing',
	},{
		title:'住房',
		icon:'icon-zhufang',
	},{
		title:'居家',
		icon:'icon-jujiashenghuo',
	},{
		title:'社交',
		icon:'icon-juhui',
	},{
		title:'旅行',
		icon:'icon-feiji',
	},{
		title:'数码',
		icon:'icon-shujuxian',
	},{
		title:'汽车',
		icon:'icon-qiche',
	},{
		title:'医疗',
		icon:'icon-yiliao',
	},{
		title:'学习',
		icon:'icon-xuexi',
	},{
		title:'礼金',
		icon:'icon-hongbao',
	},{
		title:'办公',
		icon:'icon-bangongyongpin',
	},{
		title:'维修',
		icon:'icon-weixiu',
	},{
		title:'捐赠',
		icon:'icon-gongyi01',
	},{
		title:'快递',
		icon:'icon-kuaidi1',
	}]
	const incomeType = [
		{
			title:'工资',
			icon:'icon-gongziqiarenzheng',
		},{
			title:'兼职',
			icon:'icon-jianzhi',
		},{
			title:'理财',
			icon:'icon-licai',
		},{
			title:'礼金',
			icon:'icon-hongbao',
		},{
			title:'转账',
			icon:'icon-zhuanzhang',
		},{
			title:'其它',
			icon:'icon-yulebao',
		}
	]

	
	const dataStr = (payType.map(item => `('${item.title}','${item.icon}',0)`)).join(',')+','+(incomeType.map(item => `('${item.title}','${item.icon}',1)`)).join(',')
	// 插入类型数据
	try{
		await db.insertTableData('tb_type',dataStr,col)
	}catch(e){
		//TODO handle the exception
		console.log(JSON.stringify(e))
		return false
	}
	return true
	
}
// 创建记录表
async function createRecord() {
	// 删除原数据表
	try{
		await db.dropTable('tb_record')
	}catch(e){
		//TODO handle the exception
		return false
	}
	// 创建记录表
	const data = `"id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,"type_id" INTEGER,"num" real,"create_time" text,"remark" TEXT, "image" blob DEFAULT ''`
	try{
	await db.createTable('tb_record',data)
	}catch(e){
		//TODO handle the exception
		return false
	}
	return true
}
export async function init() {
	await openDB();
	const bolType = await createType()
	const bolRecord = await createRecord()
	uni.hideLoading();
	if(bolType&&bolRecord) {
		uni.showToast({
			title:'初始化成功!',
		})
		
		uni.setStorageSync('isInitSqlite', '1');
	} else {
		uni.showModal({
			title:'提示',
			content:"初始化数据失败,请重新进入app",
			showCancel:false
		})
	}
	await closeDB()
}